The Jdbc provider can be used to connect to relational databases using jdbc. It supplies reads and writes to simple tables with acknowledgement of read rows.

The javadoc for this transport provider can be found here. And the Source Xref can be found here

Jdbc Connector properties

Jdbc Endpoint properties

Configuring queries

Sql queries are used by endpoints and should be configured on the connector or the endpoint.
Queries are stored in a map and keyed by their name.
This name will be retrieved from the address of the endpoint.

There are three types of queries:

• read queries: these are select statements
• ack queries: for each row read this query will be executed and can be an update or a deletestatement. These queries are associated with a read query. The ack query corresponding to a given read query is identified by the name of the read query followed by a .ack suffix.
• write queries: these are usually insert statements that can be used on outbound endpoints

The queries can be parameterized using a simple syntax. Parameters can be enclosed in a ${...} pattern.
When the query is executed, each parameter will be looked for in the following way:

• check if the given parameter has been included as a parameter in the endpoint uri
• if there is an input (ack and write queries), evaluate the expression as a java bean property on the input

For ack queries, the input is defined as the payload that will be sent as an UMOMessage. If you want to access a specific column that has been read, you can thus use the expression ${myColumn} which will be evaluated on the message.

For write queries, the input is defined as the UMOMessage itself. If you want to access to the whole payload, you can thus use the expression ${payload} which will be evaluated on the message.

A special expression ${NOW} can be used to create a timestamp based on the current date and time.

Using unnamed queries

Sql statements can also be executed without configuring queries. For a given endpoint (inbound or outbound), the query to execute can be specified as the address of the URI.

UMOMessage msg = eventContext.receiveEvent("jdbc://SELECT * FROM TEST", 0);

eventContext.sendEvent(msg, "jdbc://INSERT INTO TEST(TYPE, DATA) VALUES(1, ${payload})");

Using the jdbc connector

Creating the connector

To use the jdbc connector, you must first configure the connector in the mule xml configuration file.

If you use a dataSource configured in spring for example

<connector name="jdbcConnector" className="org.mule.providers.jdbc.JdbcConnector">
    <properties>
        <container-property name="dataSource" reference="myDataSource"/>
    </properties>
</connector>

DataSources can be easily created in spring using xapool:

<bean id="myDataSource"
      class="org.enhydra.jdbc.standard.StandardDataSource"
      destroy-method="shutdown">
    <property name="driverName"><value>org.hsqldb.jdbcDriver</value></property>
    <property name="url"><value>jdbc:hsqldb:file:db/test</value></property>
</bean>

else, to retrieve the dataSource from a jndi repository, you would use

<connector name="jdbcConnector" className="org.mule.providers.jdbc.JdbcConnector">
    <properties>
        <property name="jndiInitialFactory" value="..."/>
        <property name="jndiProviderUrl" value="..."/>
        <property name="dataSourceJndiName" value="..."/>
    </properties>
</connector>

Alternatively, you can use the Jndi Container to simplify configuration.

Creating queries

Sql queries used by endpoints should be configured on the connector or the endpoint.

<map name="queries">
    <property name="getTest"
        value="SELECT ID, TYPE, DATA, ACK, RESULT FROM TEST WHERE TYPE =
                ${type} AND ACK IS NULL"/>
    <property name="getTest.ack"
        value="UPDATE TEST SET ACK = ${NOW} WHERE ID = ${id} AND TYPE =
                ${type} AND DATA = ${data}" />
    <property name="writeTest"
        value="INSERT INTO TEST(ID, TYPE, DATA, ACK, RESULT) VALUES(NULL,
                ${type}, ${payload}, NULL, NULL)" />
</map>

Creating components

For a receiver,

<mule-descriptor name="..."
                 implementation="..."
                 inboundEndpoint="jdbc://getTest?type=1"
                 outboundEndpoint="..."/>

For a dispatcher

<mule-descriptor name="..."
                 implementation="..."
                 inboundEndpoint="..."
                 outboundEndpoint="jdbc://writeTest?type=1" />